<?php
/**
 * @desc sql class file
 * @author nana
 * @date 2011
 *
 */
/**
  * @usage
  *
$sql= new Niced_SQL();
$sql->set('name', 'nana');
$sql->setFrom('mysql');
$sql->setOrder('id');
$sql->setType('insert');
//var_dump($sql->render());
  */
class NICED_SQLWrapper
{
	private static $types        = array('select', 'insert', 'update', 'delete', 'replace');
	private static $callPrefixes = array('get', 'set', 'append');

	protected $type;
	protected $from;
	protected $lock;
	protected $where;
	protected $ignore;
	protected $delayed;
	protected $selectfrom;
	protected $escapeMethod;
	protected $duplicatekeyclause;

	protected $what     = array();
	protected $group    = array();
	protected $order    = array();
	protected $limit    = array();
	protected $having   = array();
	protected $useindex = array();
	protected $pairs    = array();

    public function __construct($type = 'select', $from = null)
    {
        $this->init();
        $this->setType($type);
        $this->setFrom($from);
    }

    public function init()
    {
		$this->where = new NICED_SQLWhere('AND', false);
    }

    public function toString($type = null)
	{ 
		return $this->render($type); 
	}

    public function render($type = null)
    {
        if (!is_null($type))
        {
            $type = strtolower($type);
            if (!in_array($type, self::$types))
            {
                throw new NICED_Exception('sql type is invalid');
            }
			$this->setType($type);
        }

        if (!$this->getType())
        {
            throw new NICED_Exception('no sql type');
        }

		$method = 'render'.ucfirst($this->getType());

		return $this->$method();
    }

	//set kv pair
    public function set($n, $v, $t = '')
    {
        $this->pairs[$n] = array('name' => $n, 'value' => $v, 'type' => $t);

        return $v;
    }
	
	//get kv pair
    public function get($n)
    {
        return (isset($this->pairs[$n])) ? $this->pairs[$n]['value'] : null;
    }

	public function __call($method, $args)
	{
		$method = strtolower($method);
        list($cmd, $needle) = self::resolveCall($method);

        return $this->$cmd($needle, $args);		
	}

    protected static function resolveCall($method)
    {
        $prefixes = &self::$callPrefixes;

        foreach($prefixes as $prefix)
        {
            if(substr($method, 0, strlen($prefix)) == $prefix) return array('retrieve'.ucwords($prefix), substr($method, strlen($prefix)));
        }

        throw new NICED_Exception('invalid method call to '.$method);
    }

    protected function retrieveSet($needle, $args)
	{
        $this->$needle     = array();
		$append_name       = 'append'.ucwords($needle);
		$this->$append_name($args[0]);
    }

    protected function retrieveGet($needle)
	{
        return $this->$needle;
    }

    protected function retrieveAppend($needle, $args)
	{
        $arr = &$this->$needle;
        $arr[] = trim($args[0]);
    }

    public function renderSelect() 
	{
		return $this->renderSelectStmt();
	}

    public function renderSelectStmt()
    {
        $stmt = sprintf("SELECT %s FROM `%s`", $this->getWhat(), $this->getFrom());
        $stmt .= $this->buildStmt();

        return trim($stmt);
    }

	public function buildStmt()
	{
		$type = &$this->getType();
		$stmt = '';

		if ($useindex = $this->getUseIndex())
		{
			$stmt .= sprintf(" USE INDEX (%s)", $useindex);
		}

        if ($where = $this->getWhere()) 
		{
			$stmt .= sprintf(" WHERE %s", $where);
		}

		if ('select' == $type)
		{
			if ($group = $this->getGroup()) 
			{
				$stmt .= sprintf(" GROUP BY %s", $group);
			}

			if ($having  = $this->getHaving()) 
			{
				$stmt .= sprintf(" HAVING %s", $having);
			}
		}

        if ($order = $this->getOrder()) 
		{
			$stmt .= sprintf(" ORDER BY %s", $order);
		}

		if ('select' == $type)
		{
			if ($lock  = $this->getLock()) 
			{
				$stmt .= " FOR UPDATE";
			}
		}

        if ($limit = $this->getLimit()) 
		{
			$stmt .= sprintf(" LIMIT %s", $limit);
		}

		return $stmt;
	}

    public function renderInsert()
	{ 
		return $this->renderIntoStmt('INSERT'); 
	}

    public function renderReplace()
	{ 
		return $this->renderIntoStmt('REPLACE'); 
	}

	public function renderUpdate()
    {
        $cols = array();

        foreach($this->pairs as $pair)
        {
            $cols[] = sprintf('`%s`=%s', $pair['name'], $this->format($pair['value'], $pair['type']));
        }

        $stmt = sprintf("UPDATE `%s` SET %s", $this->getFrom(), implode(", ", $cols));
        $stmt .= $this->buildStmt();

        return trim($stmt);
    }

    public function renderDelete()
    {
        $stmt = sprintf("DELETE FROM `%s`", $this->getFrom());
        $stmt .= $this->buildStmt();

        return trim($stmt);
    }

	//for insert into and replace into
    public function renderIntoStmt($type)
    {
        if($this->getIgnore()) 
		{
			$type .= ' IGNORE';
		}

        if($this->getDelayed()) 
		{
			$type .= ' DELAYED';
		}

        if ($duplicatekeyclause = $this->getDuplicatekeyclause()) 
		{
            $duplicatekeyclause = sprintf(" ON DUPLICATE KEY UPDATE %s ", $duplicatekeyclause);
		}
		else
		{
			$duplicatekeyclause = "";
		}

        if (!$this->getSelectFrom())//not insert ... select ...
        {
            $names = array();
            $values = array();

            foreach($this->pairs as $pair)
            {
                $names[]  = '`'.$pair['name'].'`';
                $values[] = $this->format($pair['value'], $pair['type']);
            }

            return sprintf("%s INTO `%s` (%s) VALUES (%s) %s", $type, $this->getFrom(), implode(', ', $names), implode(', ', $values), $duplicatekeyclause);
        }

        return sprintf("%s INTO `%s` %s", $type, $this->getFrom(), $this->renderSelectStmt());
    }

    public function appendWhat($v)
    {
        if(!is_array($v)) 
		{
			$v = explode(',', $v);
		}

        foreach($v as $k => $e)
        {
            if(preg_match('#^(.+?) as (.+?)$#i', $e, $matches)) 
			{
				list($base, $k, $e) = $matches;
			}

            $k = trim($k);
            $e = trim($e);

            if(strlen($e) < 1) 
			{
				continue;
			}

            if(is_numeric($k))
            {
                $this->what[] = $e;
            }
            else
            {
                $this->what[$k] = $e;
            }
        }

        return $this->what;
    }

	public function appendWhere($v)
    {
        if(!is_array($v)) 
		{
			$v = array($v);
		}

        foreach($v as $e)
		{
			$this->where->set($e);
		}

        return $this->where;
    }

    public function appendOrder($v)
    {
        if(!is_array($v)) 
		{
			$v = explode(',', $v);
		}

        foreach($v as $k => $e)
        {
            if(preg_match('#^(.+?) (.+?)$#i', $e, $matches)) 
			{
				list($base, $k, $e) = $matches;
			}

            $k = trim($k);
            $e = trim($e);

            if(is_numeric($k))
            {
                $this->order[] = $e;
            }
            else
            {
                $this->order[$k] = $e;
            }
        }

        return $this->order;
    }


    public function setLimit($v = NULL, $e = NULL)
    {
        if($v === NULL) 
		{
			return $this->limit = array();
		}

        if(is_numeric($v))
        {
            $this->limit = array(intval($v));

            if(is_numeric($e)) 
			{
				$this->limit[] = intval($e);
			}

            return $this->limit;
        }

        if(is_array($v)) 
		{
			$v = implode(',', $v);
		}

        if($e !== NULL) 
		{
			$v .= ','.trim($e);
		}

        $v = trim($v);

        if(preg_match('#^([0-9]+?)[ ]?,[ ]?([0-9]+?)$#', $v, $matches)) 
		{
			return $this->limit = array(intval($matches[1]), intval($matches[2]));
		}

        return $this->limit = (preg_match('#([0-9]+?)$#', $v, $matches)) ? array(intval($matches[1])) : array();
    }

    public function setSelectFrom($v)
    {
        $this->selectFrom = trim($v);
    }


    public function setDuplicateKeyClause($v)
	{
        $this->duplicatekeyclause = trim($v);
	}

    public function setWhere($v)
    {
        $this->where = new NICED_SQLWhere($this->getOperator(), false);

        return $this->appendWhere($v);
    }

    public function setOperator($v)
    {
        $this->where->setOperator($v);
    }

    public function setLock($v = true)
    {
        $this->lock = ($v) ? true : false;
    }

    public function setDelayed($v = true)
    {
    	$this->delayed = ($v) ? true : false;
    }

    public function setIgnore($v = true)
    {
    	$this->ignore = ($v) ? true : false;
    }

    public function setType($type)
    {
        if (is_null($type))
        {
            throw new NICED_Exception('type format error');
        }

        $type = strtolower($type);

		if (!in_array($type, self::$types))
		{
			throw new NICED_Exception('sql type is invalid');
		}

        return $this->type = $type;
    }

    public function setEscapeMethod($method)
	{
		$this->escapeMethod = $method;
	}

    public function getWhat()
    {
        if (!($this->what)) 
		{
			return '*';
		}

        $what = array();

        foreach($this->what as $k=>$v)
        {
            if(!is_numeric($k)) 
			{
				$v = "$k AS $v";//username as name
			}
            $what[] = $v;
        }

        return implode(', ', $what);
    }

    public function getOrder()
    {
        if (empty($this->order)) 
		{
			return '';
		}

        $order = array();

        foreach($this->order as $k => $v)
        {
            if(!is_numeric($k)) 
			{
				$v = "$k $v";//id desc
			}

            $order[] = $v;
        }

        return implode(', ', $order);
    }

    public function getOperator()
    {
        return $this->where->getOperator();
    }

    public function getFrom()
    {
        return implode(', ', $this->from);
    }

    public function getUseIndex()
    {
        return implode(', ', $this->useindex);
    }

    public function getWhere()
    {
        return $this->where->render();
    }

    public function getGroup()
    {
        return implode(', ', $this->group);
    }

    public function getHaving()
    {
        return implode(', ', $this->having);
    }

    public function getLimit()
    {
        return implode(', ', $this->limit);
    }

    public function escape($s)
    {
        if(!($this->escapeMethod)) 
		{
			return addslashes(stripslashes($s));
		}

        return call_user_func($this->escapeMethod, $s);
    }

    public function format($v, $t)
    {
        return "'".$v."'";
    }

	public function __destruct()
	{
		unset($this->where);
	}

}//end class

class NICED_SQLWhere
{
	protected $phrases;
	protected $operator;
	protected $subClause;

    public function NICED_SQLWhere($operator = 'AND', $sub_clause = true)
    {
        $this->phrases   = array();
        $this->subClause = ($sub_clause) ? true : false;
		$this->setOperator($operator);
    }

	public function toString()
	{
		return $this->render();
	}

    public function set($phrase)
    {
        if ($phrase instanceof NICED_SQLWhere) 
		{
			$phrase = $phrase->render();
		}

        if (is_scalar($phrase) && strlen($phrase) > 0) 
		{
			$this->phrases[] = $phrase;
		}
    }

    public function render()
    {
        if(empty($this->phrases)) 
		{
			return '';
		}

        $s = implode(" ".$this->operator." ", $this->phrases);

        return ($this->subClause) ? "(". $s.")" : $s;    
	}

    public function setOperator($op)
    {
        $this->operator = (strtoupper($op) == 'OR') ? 'OR' : 'AND';
    }

    public function getOperator()
    {
        return $this->operator;
    }

} //end class
